package com.extr.file.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.*;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

public class ExcelUtil {

	public static Log log = LogFactory.getLog(ExcelUtil.class);

	private static Map<String,String> transMap = new HashMap<String, String>();

	static {
		transMap.put("题目描述","题目");
		transMap.put("知识点及操作要点","知识关键点");
	}
//	public static void main(String[] args) throws Exception {
//		ExcelUtil.ExcelToMapList("/Users/zhoufm/Library/Caches/IntelliJIdea2017.3/tomcat/Unnamed_examxx/webapps/files/question/admin/tmp/编辑已审5人工智能初步--严洪超.xls");
//	}
	private static String getTranString(String value){
		String result = transMap.get(value);
		return result == null ? value : result;
	}
	private static int getMaxColumn(HSSFRow row, int start){
		if(row == null) return start;
		for(int i=start; i < row.getLastCellNum(); i++){
			if(row.getCell(i) != null){
				try{
					String value = row.getCell(i).getStringCellValue();
					if("".equals(value)){
						return i;
					}
				}
				catch (Exception ex){
					return i;
				}
			}
			else{
				return i;
			}
		}
		return row.getLastCellNum();
	}
	public static List<Map<String,String>> ExcelToMapList(String filePath) throws Exception{
		List<Map<String,String>> mapList = new ArrayList<Map<String,String>>();
//		String filetype = filePath.substring(filePath.lastIndexOf(".") + 1);
		FileInputStream fi = null;
		try {
			fi = new FileInputStream(filePath);
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		if(fi == null) {
			throw new Exception("文件不存在");
		}
		try {
			HSSFWorkbook wookbook = new HSSFWorkbook(fi);
			for(int i = 0; i < wookbook.getNumberOfSheets(); i++){
				String sheetName = wookbook.getSheetName(i);
				HSSFSheet sheet = wookbook.getSheetAt(i);
				int rows = sheet.getPhysicalNumberOfRows();
				HSSFRow title = sheet.getRow(0);
				int index = title.getFirstCellNum();
				int rowcount = getMaxColumn(title,index);
				for (int r = 1; r < rows; r++) {
					HSSFRow row = sheet.getRow(r);
					if (isBlankRow(row, index, rowcount)) {
						break;
					}
					if (row != null){
						Map<String,String> map = new TreeMap<String,String>();
						map.put("类型",sheetName);
						int cells = title.getPhysicalNumberOfCells();
						for (int c = 0; c < cells; c++){
							String value = "";
							HSSFCell cell = row.getCell(c);
							if (cell != null){
								switch (cell.getCellType()){
									case HSSFCell.CELL_TYPE_FORMULA:
										break;
									case HSSFCell.CELL_TYPE_NUMERIC:
										cell.setCellType(HSSFCell.CELL_TYPE_STRING);
										value += cell.getStringCellValue().trim();
										break;
									case HSSFCell.CELL_TYPE_STRING:
										value += cell.getStringCellValue().trim();
										break;
									default:
										value = "";
										break;
								}
							}
							if(title.getCell(c) != null) {
								map.put(getTranString(title.getCell(c).getStringCellValue().trim()), value);
							}
						}
						mapList.add(map);
					}
				}
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch(Exception e){
			e.printStackTrace();
		}
		return mapList;
	}
	public static List<Map<String,String>> ExcelToList(String filePath) throws Exception{
		
		List<Map<String,String>> mapList = new ArrayList<Map<String,String>>();
		String filetype = filePath.substring(filePath.lastIndexOf(".") + 1);
		FileInputStream fi = null;
		try {
			fi = new FileInputStream(filePath);
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		if(fi == null)
			throw new Exception("文件不存在");
		//xls文件
		if("xls".equals(filetype.toLowerCase())){
			try {
				HSSFWorkbook wookbook = new HSSFWorkbook(fi);
				HSSFSheet sheet = wookbook.getSheet("Sheet1");
				int rows = sheet.getPhysicalNumberOfRows();
				
				//获取标题行
				HSSFRow title = sheet.getRow(0);
				log.info(title.getLastCellNum());
				int index = title.getFirstCellNum();
				int rowcount = title.getLastCellNum();
				for (int i = 1; i < rows; i++){
					
					HSSFRow row = sheet.getRow(i);
					if(isBlankRow(row, index, rowcount))
						continue;
					if (row != null){
						Map<String,String> map = new TreeMap<String,String>();
						int cells = title.getPhysicalNumberOfCells();
						
						for (int j = 0; j < cells; j++){
							String value = "";
							HSSFCell cell = row.getCell(j);
							if (cell != null){
								switch (cell.getCellType()){
									case HSSFCell.CELL_TYPE_FORMULA:
										break;
									case HSSFCell.CELL_TYPE_NUMERIC:
										cell.setCellType(HSSFCell.CELL_TYPE_STRING);
										value += cell.getStringCellValue().trim();
										break;
									case HSSFCell.CELL_TYPE_STRING:
										value += cell.getStringCellValue().trim();
										break;
									default:
										value = "";
										break;
								}
							}
							//String key = title.getCell(j).getStringCellValue().trim();
							map.put(title.getCell(j).getStringCellValue().trim(), value);
						}
						
						mapList.add(map);
					}
				}
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch(Exception e){
				e.printStackTrace();
			}
		}else if("xlsx".equals(filetype.toLowerCase())){
			//xlsx文件
			try {
				XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath));
				XSSFSheet sheet = wookbook.getSheet("Sheet1");
				int rows = sheet.getPhysicalNumberOfRows();
				//获取标题行
				XSSFRow title = sheet.getRow(0);
				int index = title.getFirstCellNum();
				int rowcount = title.getLastCellNum();
				for (int i = 1; i < rows; i++){
					
					XSSFRow row = sheet.getRow(i);
					if(isBlankRow(row, index, rowcount))
						continue;
					if (row != null){
						Map<String,String> map = new TreeMap<String,String>();
						int cells = title.getPhysicalNumberOfCells();
						
						for (int j = 0; j < cells; j++){
							String value = "";
							XSSFCell cell = row.getCell(j);
							if (cell != null){
								switch (cell.getCellType()){
									case HSSFCell.CELL_TYPE_FORMULA:
										break;
									case HSSFCell.CELL_TYPE_NUMERIC:
										cell.setCellType(HSSFCell.CELL_TYPE_STRING);
										value += cell.getStringCellValue().trim();
										break;
									case HSSFCell.CELL_TYPE_STRING:
										value += cell.getStringCellValue().trim();
										break;
									default:
										value = "";
										break;
								}
							}
							map.put(title.getCell(j).getStringCellValue().trim(), value);
						}
						
						mapList.add(map);
					}
				}
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(fi != null)
			fi.close();
		
		return mapList;
	}
	
	public static boolean isBlankRow(HSSFRow row, int index, int rowCount){
		if(row == null)
			return true;
		Boolean succ = true;
		for(int i=index; i < rowCount; i++){
			if(row.getCell(i) != null){
				try{
					String value = row.getCell(i).getStringCellValue();
					succ = "".equals(value);
					if(succ == false){
						return false;
					}
				}
				catch (Exception ex){
					break;
				}
			}
		}
		return succ;
	}
	
	public static boolean isBlankRow(XSSFRow row, int index, int rowCount){
		if(row == null)
			return true;
		for(int i=index; i < rowCount; i++){
			if(row.getCell(i) != null || 
					!"".equals(row.getCell(i).getStringCellValue().trim())){
				return false;
			}
		}
		return true;
	}
}
